﻿using Dapper.Extensions;
using Microsoft.AspNetCore.Mvc;
using orm.dapper.api.Entities;

namespace orm.dapper.api.Controllers
{
    /// <summary>
    /// SQL 多表操作
    /// </summary>
    public class MultipleOfSqlController(IDapper dapper) : DapperBaseController(dapper)
    {
        [HttpPost]
        public async Task<IActionResult> Insert()
        {
            var order = new Order
            {
                Number = DateTime.Now.ToString("yyyyMMddHHmmss"),
                OrderDetails =
                [
                    new()
                    {
                        ProductName = "事务新增",
                        ProductPrice = 1.1m
                    },
                    new()
                    {
                        ProductName = "事务新增2",
                        ProductPrice = 1.1m
                    }
                ]
            };

            int orderId = 0;
            using var tran = Dapper.BeginTransaction();

            try
            {
                string orderSql = $@"
                        INSERT [{nameof(Order)}] (Number, TotalPrice, CreateTime, IsDeleted) 
                        VALUES (@number, @totalPrice, @createTime, @isDeleted)
                        SELECT SCOPE_IDENTITY()
                    ";

                orderId = await Dapper.ExecuteScalarAsync<int>(orderSql, new
                {
                    number = order.Number,
                    totalPrice = order.TotalPrice,
                    createTime = DateTime.Now,
                    isDeleted = 0
                });
                
                foreach (var detail in order.OrderDetails)
                {
                    string detailSql = $@"
                            INSERT [{nameof(OrderDetail)}] 
                            (OrderId, ProductName, ProductPrice, CreateTime) 
                            VALUES (@orderId, @productName, @productPrice, @createTime)
                        ";

                    await Dapper.ExecuteAsync(detailSql, new
                    {
                        orderId,
                        productName = detail.ProductName,
                        productPrice = detail.ProductPrice,
                        createTime = DateTime.Now
                    });
                }

                tran.Commit();
            }
            catch (Exception)
            {
                tran.Rollback();
                throw;
            }

            return Ok("Id：" + orderId);
        }

        [HttpDelete]
        public async Task<IActionResult> Delete(int id)
        { 
            using var tran = Dapper.BeginTransaction();

            try
            {
                await Dapper.ExecuteAsync(
                    $"DELETE FROM [{nameof(OrderDetail)}] WHERE OrderId = @orderId", 
                    new { orderId = id });

                await Dapper.ExecuteAsync(
                    $"DELETE FROM [{nameof(Order)}] WHERE Id = @id",
                    new { id });

                tran.Commit();
            }
            catch (Exception)
            {
                tran.Rollback();
                throw;
            }

            return Ok("Success");
        }

        [HttpGet]
        public async Task<IActionResult> List()
        {
            string sql = $@"
                SELECT * FROM [{nameof(Order)}] o
                LEFT JOIN [{nameof(OrderDetail)}] od ON o.Id = od.OrderId
                WHERE o.IsDeleted = 0
                ORDER BY o.Id DESC";

            var lookup = new Dictionary<int, Order>();

            await Dapper.QueryAsync<Order, OrderDetail, Order>(sql, (o, od) =>
            {
                if (!lookup.TryGetValue(o.Id, out var order))
                {
                    order = o;
                    order.OrderDetails = [];
                    lookup.Add(o.Id, order);
                }

                if (od != null)
                {
                    order.OrderDetails.Add(od);
                }

                return order;
            });

            return Ok(lookup.Values);
        }

        [HttpGet]
        public async Task<IActionResult> Detail(int id)
        {
            string sql = $@"
                SELECT * FROM [{nameof(Order)}] o
                LEFT JOIN [{nameof(OrderDetail)}] od ON o.Id = od.OrderId
                WHERE o.Id = @id AND o.IsDeleted = 0";

            var lookup = new Dictionary<int, Order>();

            await Dapper.QueryAsync<Order, OrderDetail, Order>(sql, (o, od) =>
            {
                if (!lookup.TryGetValue(o.Id, out var order))
                {
                    order = o;
                    order.OrderDetails = [];
                    lookup.Add(o.Id, order);
                }

                if (od != null)
                {
                    order.OrderDetails.Add(od);
                }

                return order;
            }, 
            new { id });

            return Ok(lookup.Values.FirstOrDefault());
        }
    }
}
